/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
package com.suypower.carManage.json;

import com.jarp.db.DAO;
import com.opensymphony.xwork2.ActionContext;
import com.opensymphony.xwork2.ActionSupport;
import com.suypower.carManage.VO.CarVO;
import com.suypower.carManage.VO.TollsVO;
import com.suypower.carManage.VO.TyreVO;
import com.suypower.carManage.comm.ConstUtil;
import com.suypower.carManage.comm.SQLHelp;
import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.io.UnsupportedEncodingException;
import java.util.ArrayList;
import java.util.Hashtable;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Vector;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.servlet.ServletException;
import jxl.Workbook;
import jxl.format.UnderlineStyle;
import jxl.write.Label;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
import net.sf.json.JSONArray;
import org.apache.commons.lang3.StringUtils;

/**
 *
 * @author hythzx
 */
public class TollsJson extends ActionSupport {

    private String car_code;
    private String stime;
    private String etime;
    private String start = "1";
    private String limit = "20";
    DAO dao = new DAO();
    InputStream excelStream;
    ActionContext cxt = ActionContext.getContext();
    Map<String, Object> session = cxt.getSession();
    String userid = (String) session.get("userid");
    private String excelTitle;
    private String title = "车辆过境费（停车费）";

    /**
     *
     * @return
     */
    @Override
    public String execute() {
        String sql = this.getSQL();
        int iStart = Integer.parseInt(start);
        int iPageSize = Integer.parseInt(limit);
        int iPage = iStart / iPageSize + 1;
        try {
            Vector v = dao.getDataSet(ConstUtil.DBNAME, sql + " order by t.id desc ", iPage, iPageSize);
            List<TollsVO> list = new ArrayList<TollsVO>();
            if (v != null) {
                Iterator<Hashtable> iterator = v.iterator();
                while (iterator.hasNext()) {
                    Hashtable hashtable = iterator.next();
                    TollsVO tollsVO = new TollsVO();
                    tollsVO.setCar_code((String) hashtable.get("CAR_CODE"));
                    tollsVO.setCost((String) hashtable.get("COST"));
                    tollsVO.setFee_date((String) hashtable.get("FEE_DATE"));
                    tollsVO.setId((String) hashtable.get("ID"));
                    tollsVO.setRemark((String) hashtable.get("REMARK"));
                    tollsVO.setTolls_type((String) hashtable.get("TOLLS_TYPE"));
                    tollsVO.setTolls_type_name((String) hashtable.get("TOLLS_TYPE_NAME"));
                    list.add(tollsVO);
                }
                String totalCountSQL = "select count(1) CNT from (" + sql + " ) zz ";
                String totalCount = dao.getDataValue(ConstUtil.DBNAMEODS, totalCountSQL);
                JSONArray json = JSONArray.fromObject(list);
                ActionContext.getContext().put("data", "{totalCount:" + totalCount + ",data:" + json.toString() + "}");
            } else {
                ActionContext.getContext().put("data", "{totalCount:" + 0 + ",data:''}");
            }
        } catch (ServletException ex) {
            Logger.getLogger(TollsJson.class.getName()).log(Level.SEVERE, null, ex);
        }
        return SUCCESS;
    }

    private String getSQL() {
        StringBuilder sql = new StringBuilder("SELECT t.*,a.name AS tolls_type_name FROM tb_tolls t LEFT JOIN tb_metadata a ON t.tolls_type = a.value WHERE a.code ='tolls' ");
        if (!StringUtils.isEmpty(car_code)) {
            sql.append(" and t.car_code in  (").append(SQLHelp.splitStrFromWeb(car_code)).append(")");
        }
        if (!StringUtils.isEmpty(stime)) {
            sql.append(" AND t.fee_date >= '").append(stime).append("'");
        }
        if (!StringUtils.isEmpty(etime)) {
            sql.append(" AND t.fee_date <= '").append(etime).append("'");
        }
        return sql.toString();
    }

    public String getExcel() throws UnsupportedEncodingException, IOException, WriteException {
        System.out.println("导出Excel");
        excelStream = this.getExcelInputStream();
        return "excel";

    }

    public InputStream getExcelInputStream() throws IOException, WriteException {
        ByteArrayOutputStream out = new ByteArrayOutputStream();
        create(out);
        return new ByteArrayInputStream(out.toByteArray());
    }

    public void create(OutputStream out) throws IOException, WriteException {
        DAO dao = new DAO();
        excelTitle = title + ".xls";
        excelTitle = new String(excelTitle.getBytes(), "ISO8859-1");
        List<String> count_name = new ArrayList<String>();
        ;    //用来记录具体的列名
        count_name.add("车牌号码");
        count_name.add("费用类型");
        count_name.add("费用金额");
        count_name.add("费用时间");
        count_name.add("备注");
        WritableWorkbook book = Workbook.createWorkbook(out);
        WritableSheet sheet = book.createSheet(title, 0);
        sheet.setRowView(0, 810);//设置大标题格式
        for (int i = 2; i < 100; i++) {
            sheet.setRowView(i, 320);
        }
        sheet.setColumnView(0, 18);
        sheet.setColumnView(1, 12);//设置第一行的宽度  //注意宽度和高度的单位不相同
        sheet.setColumnView(2, 30);
        sheet.setColumnView(3, 30);
        sheet.setColumnView(4, 30);
        sheet.setColumnView(5, 17);
        sheet.setColumnView(6, 17);
        sheet.setColumnView(7, 17);
        sheet.setColumnView(8, 25);
        sheet.setColumnView(9, 25);
        sheet.setColumnView(10, 35);
        sheet.setColumnView(11, 25);
        sheet.setColumnView(12, 25);
        sheet.setColumnView(13, 25);
        sheet.setColumnView(14, 25);
        sheet.setColumnView(15, 25);
        sheet.setColumnView(16, 25);
        sheet.setColumnView(17, 25);

        WritableFont wfc = new WritableFont(WritableFont.createFont("黑体"), 16, WritableFont.BOLD, false, UnderlineStyle.NO_UNDERLINE, jxl.format.Colour.BLACK);
        WritableCellFormat wcfFC = new WritableCellFormat(wfc);
        wcfFC.setAlignment(jxl.format.Alignment.CENTRE); // 设置横向对齐方式
        wcfFC.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE); //设置纵向对齐方式
        // wcfFC.setBorder(Border.ALL, BorderLineStyle.THIN);  //设置单元格边框和边框线条样式
        sheet.addCell(new Label(0, 0, title, wcfFC));

        //设置小标题格式
        WritableFont wfc2 = new WritableFont(WritableFont.createFont("黑体"), 11, WritableFont.NO_BOLD, false, UnderlineStyle.NO_UNDERLINE, jxl.format.Colour.BLACK);
        WritableCellFormat wcfFC2 = new WritableCellFormat(wfc2);
        wcfFC2.setAlignment(jxl.format.Alignment.CENTRE); // 设置横向对齐方式
        wcfFC2.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE); //设置纵向对齐方式
        wcfFC2.setBorder(jxl.format.Border.ALL, jxl.format.BorderLineStyle.THIN);  //设置单元格边框和边框线条样式
        wcfFC2.setWrap(true);

        //设置普通文字格式
        WritableFont wfc3 = new WritableFont(WritableFont.createFont("宋体"), 10, WritableFont.NO_BOLD, false, UnderlineStyle.NO_UNDERLINE, jxl.format.Colour.BLACK);
        WritableCellFormat wcfFC3 = new WritableCellFormat(wfc3);
        wcfFC3.setAlignment(jxl.format.Alignment.CENTRE); // 设置横向对齐方式
        wcfFC3.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE); //设置纵向对齐方式
        wcfFC3.setBorder(jxl.format.Border.ALL, jxl.format.BorderLineStyle.THIN);  //设置单元格边框和边框线条样式
        wcfFC3.setWrap(true);
        sheet.addCell(new Label(0, 0, title, wcfFC));
        sheet.mergeCells(0, 0, count_name.size() - 1, 0);
        for (int i = 0; i < count_name.size(); i++) {
            try {

                sheet.addCell(new Label(i, 1, count_name.get(i).split(",")[0], wcfFC2));
            } catch (Exception ex) {
            }
        }
        ArrayList<CarVO> list = new ArrayList<CarVO>();
        String sql = this.getSQL();
        Vector v = null;
        try {
            v = dao.getDataSet(ConstUtil.DBNAME, sql);

        } catch (ServletException e) {
            e.printStackTrace();
        }
        if (v != null) {

            for (int i = 1; i < v.size() + 2; i++) {
                sheet.setRowView(i, 400);
            }
            int hang = 2;     //数据应该写在多少行
            for (int i = 0; i < v.size(); i++) {
                ArrayList<String> rowlist = new ArrayList<String>();
                Hashtable hashtable = (Hashtable) v.get(i);
                TollsVO tollsVO = new TollsVO();
                tollsVO.setCar_code((String) hashtable.get("CAR_CODE"));
                tollsVO.setCost((String) hashtable.get("COST"));
                tollsVO.setFee_date((String) hashtable.get("FEE_DATE"));
                tollsVO.setId((String) hashtable.get("ID"));
                tollsVO.setRemark((String) hashtable.get("REMARK"));
                tollsVO.setTolls_type((String) hashtable.get("TOLLS_TYPE"));
                tollsVO.setTolls_type_name((String) hashtable.get("TOLLS_TYPE_NAME"));
                 rowlist.add(tollsVO.getCar_code());
                 rowlist.add(tollsVO.getTolls_type_name());
                 rowlist.add(tollsVO.getCost());
                 rowlist.add(tollsVO.getFee_date());
                 rowlist.add(tollsVO.getRemark());
                for (int i2 = 0; i2 < rowlist.size(); i2++) {
                    String thisvalue = rowlist.get(i2);
                    if (thisvalue == null || thisvalue.equals("")) {
                        thisvalue = "";
                    }
                    sheet.addCell(new Label(i2, hang, thisvalue, wcfFC3));
                }
                hang++;
            }
            book.write();
            book.close();
        }
    }

    public String getCar_code() {
        return car_code;
    }

    public void setCar_code(String car_code) {
        this.car_code = car_code;
    }

    public String getStime() {
        return stime;
    }

    public void setStime(String stime) {
        this.stime = stime;
    }

    public String getEtime() {
        return etime;
    }

    public void setEtime(String etime) {
        this.etime = etime;
    }

    public String getStart() {
        return start;
    }

    public void setStart(String start) {
        this.start = start;
    }

    public String getLimit() {
        return limit;
    }

    public void setLimit(String limit) {
        this.limit = limit;
    }

    public InputStream getExcelStream() {
        return excelStream;
    }

    public void setExcelStream(InputStream excelStream) {
        this.excelStream = excelStream;
    }

    public String getExcelTitle() {
        return excelTitle;
    }

    public void setExcelTitle(String excelTitle) {
        this.excelTitle = excelTitle;
    }

    public String getTitle() {
        return title;
    }

    public void setTitle(String title) {
        this.title = title;
    }

}
